﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Hosting;
using System.IO;
using VAdvantage.DataBase;
using VAdvantage.Model;
using VAdvantage.Utility;
using VAdvantage.ImpExp;
using VAdvantage.Classes;
using VAdvantage.Login;
using System.ServiceModel;
using VAdvantage.Logging;
using System.Data;

namespace VAdvantageSvc.Classes
{
    public class ExcelWrapper
    {
        public string FILE_PATH = HostingEnvironment.ApplicationPhysicalPath + "\\TempDownload";

        public List<String> GetExcelCols()
        {
            return null;
        }

        public string SaveBytesToFile(byte[] bytesToWrite, string filename)
        {
            try
            {
                if (filename != null && filename.Length > 0 && bytesToWrite != null)
                {
                    if (!Directory.Exists(FILE_PATH))
                        Directory.CreateDirectory(FILE_PATH);

                    FileStream file = File.Create(FILE_PATH + "\\" + filename);
                    file.Write(bytesToWrite, 0, bytesToWrite.Length);

                    file.Close();
                    //File.Open(FILE_PATH + "\\" + filename, FileMode.Open);
                }

                return FILE_PATH + "\\" + filename;
            }
            catch
            {
            }

            return null;
        }



        public List<string> ConnectExcel(byte[] bytesToWrite, string filename)
        {
            List<String> excelCOLS = new List<string>();
            string f = SaveBytesToFile(bytesToWrite, filename);
            if (!string.IsNullOrEmpty(f))
            {
                VAdvantage.ImpExp.ExcelReader excelReader = new VAdvantage.ImpExp.ExcelReader(1, 1);
                bool bln = excelReader.TestConnection(f);

                if (!bln)
                    return null;
                else
                {
                    string[] sheets = excelReader.GetSheets();  //Get all the sheets from an excel file
                    if (sheets != null)
                    {
                        System.Data.DataTable dt = excelReader.ExtractDataTable(f, sheets[0]);
                        for (int d = 0; d <= dt.Columns.Count - 1; d++)
                        {
                            excelCOLS.Add(dt.Columns[d].ToString());
                        }

                    }

                    return excelCOLS;
                }
            }

            return null;
        }

        public Dictionary<int, String> LoadWindowDetail(int WindowID, out List<Dictionary<int, string>> list, out List<List<bool>> identifierList, out List<int> TabList, Ctx ctx, out List<Dictionary<int, string>> columns, out Dictionary<int, String> tables)
        {
            int indexer = 0;
            //list = null;
            List<Dictionary<int, string>> templist = new List<Dictionary<int, string>>();
            List<Dictionary<int, string>> templist1 = new List<Dictionary<int, string>>();

            identifierList = new List<List<bool>>();
            columns = new List<Dictionary<int, string>>();
            TabList = new List<int>();

            StringBuilder sb = new StringBuilder("SELECT DISTINCT TBL.AD_TABLE_ID, TBL.TABLENAME || ' ( ' || TABS.NAME || ' ) ' AS TABLENAME, TABS.AD_TAB_ID, TABS.SEQNO, TBL.TABLENAME as ONLYTABLENAME, TABS.ISINFOTAB, TABS.ISADVANCEDTAB FROM ")
            .Append("AD_TABLE TBL ")
            .Append("INNER JOIN AD_TAB TABS ON TBL.AD_TABLE_ID = TABS.AD_TABLE_ID ")
            .Append("INNER JOIN AD_WINDOW WIN ON TABS.AD_WINDOW_ID = WIN.AD_WINDOW_ID ")
            .Append("WHERE ");

            //if (ctx.GetContext("#ShowAcct").Equals("Y", StringComparison.OrdinalIgnoreCase))
            //{
            //    sb.Append(" TABS.ISINFOTAB = 'Y' AND ");
            //}

            //if (ctx.GetContext("#ShowAdvanced").Equals("Y", StringComparison.OrdinalIgnoreCase))
            //{
            //    sb.Append(" TABS.ISADVANCEDTAB = 'Y' AND ");
            //}

            sb.Append(" TABS.ISACTIVE='Y' AND TABS.AD_WINDOW_ID=@WindowID ORDER BY TABS.SEQNO ");


            string showAcct = ctx.GetContext("#ShowAcct");
            System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[1];
            param[0] = new System.Data.SqlClient.SqlParameter("@WindowID", WindowID);

            Dictionary<int, string> windows = new Dictionary<int, string>();
            Dictionary<int, string> onlytables = new Dictionary<int, string>();
            System.Data.IDataReader dr = DB.ExecuteReader(sb.ToString(), param);
            while (dr.Read())
            {
                if (showAcct.Equals("N") && dr["ISINFOTAB"].ToString().Equals("Y"))
                {
                    continue;
                }

                int tempID = int.Parse(dr[0].ToString());

                if (windows.ContainsKey(int.Parse(dr[0].ToString())))
                {
                    tempID = int.Parse(dr[0].ToString() + indexer.ToString() + "00000");
                    indexer++;
                }

                windows.Add(tempID, dr["TABLENAME"].ToString());
                onlytables.Add(tempID, dr["ONLYTABLENAME"].ToString());

                TabList.Add(int.Parse(dr["AD_TAB_ID"].ToString()));
                string sql = @" SELECT cc.AD_Column_ID,
                              cc.ColumnName
                              || ' ('
                              ||
                              (SELECT ad.Name
                                 FROM ad_reference ad
                                WHERE ad.ad_reference_id = cc.ad_reference_id
                              )
                              || ')' AS ColumnWithRef,
                              cc.ColumnName          ,
                              cc.AD_Reference_ID     ,
                              cc.ColumnName
                              || (
                              CASE 
                                when f.name IS NULL
                                THEN ''
                                ELSE to_char (' ('
                                  || f.Name
                                  || ')')
                              END)
                              || (
                              CASE cc.Ismandatory
                                WHEN 'Y'
                                THEN ' *'
                                ELSE ''
                              END) AS ColumnWithField,  cc.ColumnName
                               FROM AD_Column cc
                            LEFT OUTER JOIN ad_field f
                                 ON cc.Ad_Column_Id = f.AD_Column_ID
                            AND f.Ad_Tab_ID         = " + dr[2].ToString() + @"
                              WHERE CC.IsActive = 'Y' AND cc.AD_Table_ID  = " + dr[0].ToString();

                Dictionary<int, string> retVal = new Dictionary<int, string>();
                Dictionary<int, string> retVal1 = new Dictionary<int, string>();
                List<bool> isIdentifier = new List<bool>();
                System.Data.IDataReader drinn = DB.ExecuteReader(sql);
                while (drinn.Read())
                {
                    isIdentifier.Add(MColumn.Get(ctx, int.Parse(drinn[0].ToString())).IsIdentifier());
                    retVal.Add(int.Parse(drinn[0].ToString()), drinn["ColumnWithField"].ToString());
                    retVal1.Add(int.Parse(drinn[0].ToString()), drinn["ColumnName"].ToString());

                }
                drinn.Close();

                templist.Add(retVal);
                templist1.Add(retVal1);
                identifierList.Add(isIdentifier);
            }
            dr.Close();

            list = templist;
            columns = templist1;
            tables = onlytables;
            return windows;

        }


        #region Import Process

        private string m_SheetName = "Sheet$1";
        /* Selected File Name to be imported into Oracle Table */
        private string m_fileName = "";
        /* Current Context */
        private Ctx _ctx = null;
        //To maintain the state of window combobox (while loading)
        bool _blockWindow = false;
        bool _blockTable = false;
        /* Selected table Name */
        private string m_TableName = "";
        private int m_TableID;
        /* stores mappeded references */
        List<int> mappedReference = new List<int>();
        /* Mapped columsn entries ID */
        List<MColumn> m_MappedCols = new List<MColumn>();
        /* Org of Mapped Columns */
        List<int> m_MappedOrgs = new List<int>();
        /* Mapped columsn entries Names */
        List<string> m_MappedColsName = new List<string>();
        /* Mapped columsn entries */
        List<int> m_MappedExcelCols = new List<int>();
        /* Mapped columsn entries */
        List<int> m_CurrentExcelMapping = new List<int>();
        /* Order of mapping */
        List<MColumn> m_MappedColsInOrder = new List<MColumn>();
        /* Tabs of mapped record */
        List<MTable> m_MappedTabs = new List<MTable>();
        /* Tabs of mapped record */
        List<MTable> m_WindowTables = new List<MTable>();
        List<MTab> m_WindowTabs = new List<MTab>();
        /*Current Window ID */
        private int m_windowID;

        private List<MColumn> m_identifierColumn = new List<MColumn>();

        private List<string> m_MappedExcelColName = new List<string>();


        private int AD_Org_ID = 0;
        private int AD_Client_ID = 0;

        List<int> m_ParentKeys = new List<int>();   //contains the primary keys 
        List<int> m_multipleResults = new List<int>();

        List<string> m_errorStatus = new List<string>();
        List<string> m_errorLog = new List<string>();
        List<string> m_errorInQuery = new List<string>();

        string sWhereClause = "";

        private Trx _trx;
        private System.Data.DataTable m_ImportedData = new System.Data.DataTable();
        private System.Data.DataTable m_ErroredData = new System.Data.DataTable();
        private System.Data.DataTable m_FinalData;
        int mapCount = 0;
        private List<MTable> m_QueiredTable = new List<MTable>();


        private List<List<int>> m_ParentIDs = new List<List<int>>();
        private List<List<string>> m_Queries = new List<List<string>>();

        private List<string> m_tempQueryHolder = new List<string>();
        /* Errors encountered while inserting */
        List<string> m_insertErrors = new List<string>();

        private int m_MapIndex = -1;
        private List<string> m_ExcelColNames = new List<string>();

        ImportClientInfoModel icm = null;

        /// <summary>
        /// Get the first block of insert query
        /// </summary>
        /// <returns>string</returns>
        private string GetInitialInsertQuery(int m_CurrentTableID)
        {
            m_MappedColsInOrder.Clear();
            MTable table = new MTable(_ctx, Convert.ToInt32(m_CurrentTableID), null);
            MColumn[] columns = table.GetColumns(true);
            StringBuilder sbQuery = new StringBuilder("INSERT INTO " + table.GetTableName())
            .Append(" (");
            StringBuilder sbNotMapped = new StringBuilder("");

            m_CurrentExcelMapping.Clear();
            //sort the order of columns
            for (int i = 0; i <= m_MappedCols.Count() - 1; i++)
            {
                if (m_CurrentTableID == m_MappedTabs[i].GetAD_Table_ID())
                {
                    m_CurrentExcelMapping.Add(m_MappedExcelCols[i]);
                    m_MappedColsInOrder.Add(m_MappedCols[i]);
                    sbQuery.Append(m_MappedCols[i].GetColumnName());
                    //if (!(i == columns.Length - 1))
                    sbQuery.Append(",");
                }
            }


            for (int i = 0; i <= columns.Length - 1; i++)
            {
                if (columns[i].IsVirtualColumn())
                    continue;

                if (!IsMapped(columns[i]))
                {
                    if (columns[i].GetColumnName() != "Created"
                         && columns[i].GetColumnName() != "CreatedBy"
                         && columns[i].GetColumnName() != "Updated"
                         && columns[i].GetColumnName() != "UpdatedBy"
                         && columns[i].GetColumnName() != "AD_Client_ID"
                         && columns[i].GetColumnName() != "AD_Org_ID")
                    {
                        //m_MappedColsInOrder.Add(columns[i]);
                        sbNotMapped.Append(columns[i].GetColumnName());
                        if (!(i == columns.Length - 1))
                            sbNotMapped.Append(",");

                        m_MappedColsInOrder.Add(columns[i]);
                    }
                }
            }

            if (sbNotMapped.ToString().LastIndexOf(",") == sbNotMapped.Length - 1)
            {
                //sbQuery.Append(",")
                sbQuery.Append(sbNotMapped.ToString().Substring(0, sbNotMapped.Length - 1));
                return sbQuery.Append(")").ToString();
            }

            sbQuery.Append(sbNotMapped.ToString())
                .Append(")");

            return sbQuery.ToString();
        }

        /// <summary>
        /// Check if the db column is mapped
        /// </summary>
        /// <param name="column">Column to be checked for mapping</param>
        /// <returns></returns>
        private bool IsMapped(MColumn column)
        {
            if (m_MappedCols.Contains(column))
                return true;

            return false;
        }

        private string MappedWith(MColumn column)
        {
            //for (int i = 0; i <= m_ExcelColNames.Count() - 1; i++)
            //{
            int index = m_MappedCols.IndexOf(column);
            return m_ExcelColNames[index];
            //}
        }

        /// <summary>
        /// Check if default value is set
        /// </summary>
        /// <param name="defValue"></param>
        /// <returns></returns>
        private bool IsDefaultValueSet(string defValue)
        {
            if (String.IsNullOrEmpty(defValue))
                return false;
            else if (defValue.StartsWith("@"))
                return false;

            return true;
        }

        private string GetOrgQuery(string dynSql, string tableName)
        {
            MRole role = MRole.Get(_ctx, _ctx.GetAD_Role_ID());
            return role.AddAccessSQL(dynSql, tableName, false, false);

        }

        #region Load Saved Mapping
        private void LoadSavedMapping()
        {
            try
            {
                ExcelReader reader = new ExcelReader(1, 1);
                string[] sheets = reader.GetSheets();  //Get all the sheets from an excel file
                System.Data.DataTable dt = reader.ExtractDataTable(FILE_PATH + "\\" + icm.FileName, "Sheet1$");
                string sql = "SELECT * FROM AD_IMPORTMAP WHERE AD_WINDOW_ID = " + icm.MWindowID + " AND AD_Client_ID = " + _ctx.GetAD_Client_ID() + " ORDER BY AD_IMPORTMAP_ID";
                System.Data.IDataReader dr = DB.ExecuteReader(sql);
                while (dr.Read())
                {
                    m_MappedCols.Add(MColumn.Get(_ctx, int.Parse(dr["AD_COLUMN_ID"].ToString())));
                    m_MappedOrgs.Add(int.Parse(dr["AD_Org_ID"].ToString()));
                    m_ExcelColNames.Add(dr["EXCELCOLNAME"].ToString());
                    m_MappedExcelCols.Add(dt.Columns.IndexOf(dr["EXCELCOLNAME"].ToString()));
                    m_WindowTabs.Add(MTab.Get(_ctx, int.Parse(dr["AD_TAB_ID"].ToString())));
                    m_MappedColsName.Add(MColumn.Get(_ctx, int.Parse(dr["AD_COLUMN_ID"].ToString())).GetColumnName());
                    m_MappedTabs.Add(MTable.Get(_ctx, Convert.ToInt32(dr["AD_TABLE_ID"].ToString())));
                    m_MappedExcelColName.Add(int.Parse(dr["AD_COLUMN_ID"].ToString()) + "-" + dt.Columns.IndexOf(dr["EXCELCOLNAME"].ToString()));
                    m_MapIndex++;
                }
                dr.Close();

                sql = "SELECT AD_Column_ID FROM AD_ImportIdentifier WHERE IsActive='Y' and AD_WINDOW_ID = " + icm.MWindowID + sWhereClause + " ORDER BY AD_ImportIdentifier_ID";

                dr = DB.ExecuteReader(sql);
                int count = 0;
                while (dr.Read())
                {
                    //m_TableID
                    MColumn curCol = MColumn.Get(_ctx, int.Parse(dr[0].ToString()));
                    int index = m_WindowTables.IndexOf(MTable.Get(_ctx, curCol.GetAD_Table_ID()));

                    m_identifierColumn[index] = curCol;
                    count++;
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                m_MappedCols.Clear();
                m_ExcelColNames.Clear();
                m_MappedExcelCols.Clear();
                m_WindowTabs.Clear();
                m_MappedColsName.Clear();
                m_MappedTabs.Clear();
                m_MappedExcelColName.Clear();


                //callback to the client
                callback.QueryExecuted(new CallbackDetail()
                {
                    Status = "Error occurred! Import stopped",
                    Query = ex.Message,
                    Error = ""
                });


            }
        }
        #endregion

        #region Save Import Detail

        public bool SaveImportDetail()
        {
            CallbackDetail cb = new CallbackDetail();
            cb.Status = "Saving import details";
            cb.Query = "";
            cb.Error = "";

            callback.QueryExecuted(cb);

            string delSql = "DELETE FROM AD_IMPORTMAP WHERE AD_WINDOW_ID = " + icm.MWindowID + sWhereClause;
            int res = DB.ExecuteQuery(delSql);

            List<int> save = new List<int>();
            for (int i = 0; i <= icm.m_MappedColumns.Count() - 1; i++)
            {
                int AD_Table_ID = icm.m_MappedTables[i];
                int AD_Tab_ID = icm.TabList[0];
                int AD_Excel_Col = icm.m_ExcelColIndex[i];
                int AD_Org_ID = _ctx.GetAD_Org_ID();
                string AD_Excel_ColName = icm.m_ExcelColName[i];
                int AD_Column_ID = icm.m_MappedColumns[i];
                int AD_IMPORTMAP_ID = MSequence.GetNextID(_ctx.GetAD_Client_ID(), "AD_IMPORTMAP", null);
                string sql = @"INSERT INTO AD_IMPORTMAP 
                                   (AD_IMPORTMAP_ID,
                                    AD_CLIENT_ID,
                                    AD_COLUMN_ID,
                                    AD_ORG_ID,
                                    AD_TABLE_ID,
                                    AD_WINDOW_ID,
                                    CREATED,
                                    CREATEDBY,
                                    EXCELCOLINDEX,
                                    EXCELCOLNAME,
                                    ISACTIVE,
                                    UPDATED,
                                    UPDATEDBY,
                                    AD_TAB_ID) VALUES 
                                (" + AD_IMPORTMAP_ID + ", " +
                                   _ctx.GetAD_Client_ID() + ", " +
                                   AD_Column_ID + ", " +
                                   AD_Org_ID + ", " +
                                   AD_Table_ID + ", " +
                                   icm.MWindowID + ", SYSDATE, " +
                                   _ctx.GetAD_User_ID() + ", " + AD_Excel_Col + ", '" +
                                   AD_Excel_ColName + "', 'Y', SYSDATE, " +
                                   _ctx.GetAD_User_ID() + ", " +
                                   AD_Tab_ID + ")";

                int resInsert = DB.ExecuteQuery(sql);
                save.Add(resInsert);
            }


            for (int i = 0; i <= icm.m_identifierColumn.Count - 1; i++)
            {
                if (icm.m_identifierColumn[i] > 0)
                {
                    delSql = "DELETE FROM AD_ImportIdentifier WHERE AD_Window_ID = " + icm.MWindowID + sWhereClause + " AND AD_Table_ID = " + icm.m_AllTables[i];
                    res = DB.ExecuteQuery(delSql);

                    int AD_IDENTIFIER_ID = MSequence.GetNextID(_ctx.GetAD_Client_ID(), "AD_IMPORTIDENTIFIER", null);
                    string sql = @"INSERT INTO AD_ImportIdentifier 
                                    (AD_ImportIdentifier_ID,
                                    AD_CLIENT_ID,
                                    AD_ORG_ID,
                                    AD_TABLE_ID,
                                    CREATED,
                                    CREATEDBY,
                                    ISACTIVE,
                                    UPDATED,
                                    UPDATEDBY,
                                    AD_Column_ID, AD_WINDOW_ID) Values 
                                (" + AD_IDENTIFIER_ID + ", " +
                                       _ctx.GetAD_Client_ID() + ", " +
                                       _ctx.GetAD_Org_ID() + ", " +
                                       icm.m_AllTables[i] + ", " +
                                       " SYSDATE, " +
                                       _ctx.GetAD_User_ID() + ", " +
                                       "'Y', SYSDATE, " +
                                       _ctx.GetAD_User_ID() + ", " +
                                       icm.m_identifierColumn[i] + ", " + icm.MWindowID + ")";

                    int resInsert = DB.ExecuteQuery(sql);
                }
            }

            cb.Status = "Import detail Saved";
            cb.Query = "";
            cb.Error = "";

            callback.QueryExecuted(cb);

            if (save.Contains(-1))
                return false;
            else
                return true;
        }

        #endregion

        public void LoadMapInfo(int MapID, Ctx ctx)
        {
            ExcelReader reader = new ExcelReader(1, 1);
            string[] sheets = reader.GetSheets();  //Get all the sheets from an excel file
            System.Data.DataTable dt = reader.ExtractDataTable(FILE_PATH + "\\" + icm.FileName, "Sheet1$");

            String _sql = "SELECT * FROM AD_ExcelImportMapping WHERE IsActive = 'Y' AND AD_Client_ID = " + ctx.GetAD_Client_ID() + " AND AD_ExcelImport_ID = " + MapID;
            IDataReader dr = DB.ExecuteReader(_sql);
            int count = 0;
            while (dr.Read())
            {
                MColumn mapcol = MColumn.Get(_ctx, int.Parse(dr["AD_COLUMN_ID"].ToString()));
                m_MappedCols.Add(mapcol);
                m_MappedOrgs.Add(int.Parse(dr["AD_Org_ID"].ToString()));
                m_ExcelColNames.Add(dr["EXCELCOLNAME"].ToString());
                m_MappedExcelCols.Add(dt.Columns.IndexOf(dr["EXCELCOLNAME"].ToString()));
                m_WindowTabs.Add(MTab.Get(_ctx, int.Parse(dr["AD_TAB_ID"].ToString())));
                m_MappedColsName.Add(MColumn.Get(_ctx, int.Parse(dr["AD_COLUMN_ID"].ToString())).GetColumnName());
                m_MappedTabs.Add(MTable.Get(_ctx, Convert.ToInt32(dr["AD_TABLE_ID"].ToString())));
                m_MappedExcelColName.Add(int.Parse(dr["AD_COLUMN_ID"].ToString()) + "-" + dt.Columns.IndexOf(dr["EXCELCOLNAME"].ToString()));
                m_MapIndex++;

                
                if (dr["ISIDENTIFIER"].Equals("Y"))
                {
                    int index = m_WindowTables.IndexOf(MTable.Get(_ctx, mapcol.GetAD_Table_ID()));
                    m_identifierColumn[index] = mapcol;
                    count++;
                }
            }
            dr.Close();
        }

        public bool StartImport(ImportClientInfoModel clientinfomodel, out byte[] excelfile, List<Dictionary<String, Object>> DefaultValues, LocationMappingDetail LocationMapping)
        {
            _ctx = clientinfomodel.ctx;
            for (int i = 0; i <= clientinfomodel.m_AllTables.Count - 1; i++)
            {
                m_WindowTables.Add(MTable.Get(_ctx, clientinfomodel.m_AllTables[i]));
                m_identifierColumn.Add(null);
            }

            sWhereClause = " AND AD_Client_ID = " + clientinfomodel.ctx.GetAD_Client_ID();
            icm = clientinfomodel;

            //if(clientinfomodel.OnlySave)
            callback = OperationContext.Current.GetCallbackChannel<IImportCallback>();

            SaveImportDetail();

            if (!clientinfomodel.OnlySave)
            {
                LoadSavedMapping();
                DoWork(DefaultValues, LocationMapping);

                excelfile = null;
                if (m_ImportedData.Rows.Count <= 0 && m_ErroredData.Rows.Count <= 0)
                {
                    return false;
                }
                m_insertErrors.RemoveAll(s => ((string)s).Equals("NoError"));

                if (m_insertErrors.Count > 0)
                {
                    return false;
                }
                else
                    return true;
            }

            excelfile = null;
            return true;

        }


        IImportCallback callback = null;

        private void DoWork(List<Dictionary<String, Object>> DefaultValues, LocationMappingDetail LocationMapping)
        {
            if(callback == null)
                callback = OperationContext.Current.GetCallbackChannel<IImportCallback>();

            //callback to the client
            callback.QueryExecuted(new CallbackDetail()
            {
                Status = "Importing now..",
                Query = "",
                Error = ""
            });

            List<MTable> m_finalMappedTables = new List<MTable>();  //List of all the table Mapped in a window

            //following loop checks the table mapped and puts into m_finalMappedTables List.
            for (int tables = 0; tables <= m_WindowTables.Count - 1; tables++)
            {
                if (IsTableMapped(m_WindowTables[tables]))
                {
                    if (!m_finalMappedTables.Contains(m_WindowTables[tables]))
                        m_finalMappedTables.Add(m_WindowTables[tables]);
                }
            }
            int loopCount = 0;
            m_insertErrors.Clear(); //Clear all the previous errors from the List
            int m_AD_Client_ID = _ctx.GetAD_Client_ID();
            int m_AD_Org_ID = _ctx.GetAD_Org_ID();

            ExcelReader reader = new ExcelReader(1, 1);
            System.Data.DataTable dt = reader.ExtractDataTable(FILE_PATH + "\\" + icm.FileName, "Sheet1$");    //extract all the records form excel to DataTable

            if (dt.Rows.Count <= 0 || dt == null)
                return;

            m_ImportedData = dt.Clone();
            m_ErroredData = dt.Clone();

            List<List<string>> m_ParentValue = new List<List<string>>();    //Window will have parent tab. so to keep parent tab ID's we'll use m_Parent Value List
            for (int tables = 0; tables <= m_WindowTables.Count - 1; tables++)
            {
                m_ParentValue.Add(new List<string>());
                string keyColumn = m_WindowTables[tables].GetTableName() + "_ID";
                string updateQuery = "UPDATE " + m_WindowTables[tables].GetTableName() + " SET " + keyColumn + " = 909 WHERE " + keyColumn + "= 909";
                if (!IsTableMapped(m_WindowTables[tables]) || m_QueiredTable.Contains(m_WindowTables[tables]))
                    continue;

                loopCount++;
                m_QueiredTable.Add(m_WindowTables[tables]);
                m_tempQueryHolder.Clear();
                m_ParentKeys.Clear();
                StringBuilder sql = new StringBuilder("");
                sql.Append(GetInitialInsertQuery(m_WindowTables[tables].Get_ID()));

                int s_TotalRows = dt.Rows.Count;
                string val = "";
                MTable table = new MTable(_ctx, Convert.ToInt32(m_WindowTables[tables].GetAD_Table_ID()), null);
                //MColumn[] columns = table.GetColumns(true);

                m_FinalData = new System.Data.DataTable();

                //foreach (MColumn column in m_MappedColsInOrder)
                //{
                //    m_FinalData.Columns.Add(column.GetColumnName());
                //}

                List<string> m_checkDuplicate = new List<string>();
                List<int> m_duplicateKey = new List<int>();
                List<String> m_DuplicateParentValue = new List<String>();

                StringBuilder anyError = new StringBuilder("");
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    bool isLoopBroken = false;

                    anyError.Remove(0, anyError.Length);
                    mapCount = 0;
                    System.Data.DataRow row = m_FinalData.NewRow();

                    try
                    {
                        //bgWorker.ReportProgress(0);

                        StringBuilder sbValue = new StringBuilder(" VALUES (");
                        StringBuilder sbFinalQuery = new StringBuilder();

                        int key = MSequence.GetNextID(m_AD_Client_ID, table.GetTableName(), "ImportProcess");
                        for (int cols = 0; cols <= m_MappedColsInOrder.Count - 1; cols++)
                        {
                            int progValue = 100 / (m_MappedColsInOrder.Count - 1);
                            try
                            {
                                //we will have two condition to check
                                //1. Columns which are mapped
                                //2. Columns which are not mapped
                                if (IsMapped(m_MappedColsInOrder[cols]))    //checks if column is mapped or not
                                {
                                    val = dt.Rows[i][m_CurrentExcelMapping[mapCount]].ToString();
                                    string identifierColumn = "";
                                    if (m_identifierColumn[tables] != null)
                                    {
                                        identifierColumn = m_identifierColumn[tables].GetColumnName();
                                    }

                                    if (m_MappedColsInOrder[cols].GetColumnName().Equals(identifierColumn, StringComparison.OrdinalIgnoreCase))
                                    {
                                        string checkValue = val;
                                        if (tables > 0)
                                        {
                                            int tableIndex = 0;
                                            for (int loop = 0; loop <= m_MappedColsInOrder.Count - 1; loop++)
                                            {
                                                if (m_MappedColsInOrder[loop].GetColumnName().EndsWith("_ID"))
                                                {
                                                    string colValue = m_MappedColsInOrder[loop].GetColumnName().Replace("_ID", "");
                                                    if (colValue.Equals(m_WindowTables[tables].GetTableName(), StringComparison.OrdinalIgnoreCase))
                                                        continue;

                                                    MTable foundTable = MTable.Get(_ctx, colValue);
                                                    if (m_finalMappedTables.Contains(foundTable))
                                                    {
                                                        tableIndex = m_finalMappedTables.IndexOf(foundTable);
                                                        break;
                                                    }
                                                }
                                            }
                                            checkValue = dt.Rows[i][MappedWith(m_identifierColumn[tableIndex])].ToString() + "|^|" + val;
                                        }

                                        if (m_checkDuplicate.Contains(checkValue))
                                        {
                                            key = m_duplicateKey[m_checkDuplicate.IndexOf(checkValue)];
                                            isLoopBroken = true;
                                            break;
                                        }



                                        //m_DuplicateTable.Add(m_WindowTables[tables]);
                                        String firstSql = "SELECT " + m_WindowTables[tables].GetTableName() + "_ID FROM " + m_WindowTables[tables].GetTableName();

                                        if (m_identifierColumn[tables] != null)
                                            firstSql += " WHERE " + m_identifierColumn[tables].GetColumnName() + "='" + val + "' ";


                                        if (tables > 0)
                                        {
                                            int tableIndex = 0;
                                            for (int loop = 0; loop <= m_MappedColsInOrder.Count - 1; loop++)
                                            {
                                                try
                                                {
                                                    if (m_MappedColsInOrder[loop].GetColumnName().EndsWith("_ID"))
                                                    {
                                                        string colValue = m_MappedColsInOrder[loop].GetColumnName().Replace("_ID", "");
                                                        if (colValue.Equals(m_WindowTables[tables].GetTableName(), StringComparison.OrdinalIgnoreCase))
                                                            continue;

                                                        MTable foundTable = MTable.Get(_ctx, colValue);

                                                        if (m_finalMappedTables.Contains(foundTable))
                                                        {
                                                            tableIndex = m_finalMappedTables.IndexOf(foundTable);
                                                            int id = m_ParentIDs[tableIndex][i];
                                                            firstSql = "SELECT " + m_WindowTables[tables].GetTableName() + "_ID FROM " + m_WindowTables[tables].GetTableName() + " WHERE " + m_WindowTables[tableIndex].GetTableName() + "_ID =" + id + " AND " + m_identifierColumn[tables].GetColumnName() + "='" + val + "' ";
                                                        }
                                                    }
                                                }
                                                catch
                                                {
                                                }
                                            }

                                        }
                                        firstSql = GetOrgQuery(firstSql, m_WindowTables[tables].GetTableName());
                                        System.Data.IDataReader dr = DB.ExecuteReader(firstSql);
                                        int val_id = 0;
                                        int j = 0;
                                        while (dr.Read())
                                        {
                                            val_id = int.Parse(dr[0].ToString());
                                            j++;

                                            if (j > 1)
                                            {
                                                //m_multipleResults.Add(i);
                                                string fkTable = m_WindowTables[tables].GetTableName();
                                                if (m_MappedColsInOrder[cols].GetFKTable() != null)
                                                    fkTable = m_MappedColsInOrder[cols].GetFKTable().GetTableName();
                                                anyError.Append("MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ")").Append(" IN ").Append(fkTable).Append(", ");

                                                m_errorLog.Add(val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") IN " + fkTable);
                                                m_errorStatus.Add("MutltipleRecordFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                                m_errorInQuery.Add("MultipleRecordFoundFor: " + val);
                                            }

                                        }
                                        dr.Close();

                                        //  Check if the record already exists in database    //
                                        string sqlCheck = "SELECT * FROM " + m_WindowTables[tables].GetTableName() + " WHERE " + m_MappedColsInOrder[cols].GetColumnName().ToUpper() + " = @val AND " + m_WindowTables[tables].GetTableName() + "_ID = " + val_id;
                                        sqlCheck = GetOrgQuery(sqlCheck, m_WindowTables[tables].GetTableName());
                                        System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[1];
                                        param[0] = new System.Data.SqlClient.SqlParameter("@val", val);
                                        dr = DB.ExecuteReader(sqlCheck, param);
                                        while (dr.Read())
                                        {
                                            key = int.Parse(dr[m_WindowTables[tables].GetTableName() + "_ID"].ToString());
                                            isLoopBroken = true;
                                            break;
                                        }
                                        dr.Close();

                                        m_duplicateKey.Add(key);
                                        m_checkDuplicate.Add(checkValue);

                                        if (isLoopBroken)
                                            break;
                                    }


                                    mapCount++;
                                    if (m_MappedColsInOrder[cols].IsStringOrText())
                                    {
                                        sbValue.Append(GlobalVariable.TO_STRING(val));
                                    }
                                    else if (m_MappedColsInOrder[cols].IsDateTime())
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Parse(val), false));
                                            val = DateTime.Parse(val).ToString("dd-MMM-yyyy");
                                        }
                                        else
                                        {
                                            sbValue.Append("NULL");
                                            val = "NULL";

                                        }
                                    }
                                    else if (FieldType.IsNumeric(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {

                                        if (!string.IsNullOrEmpty(val))
                                            sbValue.Append(val);
                                    }
                                    else if (m_MappedColsInOrder[cols].IsYesNo())
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            if (val.Equals("True", StringComparison.OrdinalIgnoreCase))
                                            {
                                                sbValue.Append(GlobalVariable.TO_STRING("Y"));
                                                row[cols] = "Y";
                                                val = "Y";
                                            }
                                            else if (val.Equals("Y", StringComparison.OrdinalIgnoreCase))
                                            {
                                                sbValue.Append(GlobalVariable.TO_STRING("Y"));
                                                row[cols] = "Y";
                                                val = "Y";
                                            }
                                            else
                                            {
                                                sbValue.Append(GlobalVariable.TO_STRING("N"));
                                                val = "N";
                                            }

                                        }
                                        else
                                        {
                                            sbValue.Append(m_MappedColsInOrder[cols].GetSQLDefaultValue());
                                            val = m_MappedColsInOrder[cols].GetSQLDefaultValue();
                                        }
                                    }
                                    else if (FieldType.IsID(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {
                                        MTable refTable = null;
                                        try
                                        {
                                            if (m_MappedColsInOrder[cols].GetAD_Reference_ID().Equals(30))
                                            {
                                                //If the column type is "SEARCH", we will try to find it through it ref value key.

                                                //Find the Reference Detail of a column
                                                MReference refOtherway = MReference.Get(_ctx, m_MappedColsInOrder[cols].GetAD_Reference_Value_ID());

                                                if (refOtherway.Get_ID().Equals(0)) //if null, just append null to the value query
                                                {
                                                    String FKTableName = m_MappedColsInOrder[cols].GetColumnName();
                                                    if (FKTableName.EndsWith("_ID"))
                                                        FKTableName = FKTableName.Substring(0, FKTableName.Length - 3);
                                                    refTable = MTable.Get(_ctx, FKTableName);
                                                    if (refTable == null)
                                                    {
                                                        sbValue.Append("NULL").Append(", ");
                                                        continue;
                                                    }
                                                }
                                                if (!refOtherway.Get_ID().Equals(0))
                                                {
                                                    if (refOtherway.GetValidationType().Equals("T"))    //run only in case of "T" Validation Type
                                                    {
                                                        //Find the Table refered in Validation 
                                                        MRefTable findTable = MRefTable.Get(_ctx, refOtherway.GetAD_Reference_ID());
                                                        if (findTable == null)
                                                        {
                                                            sbValue.Append("NULL").Append(", ");
                                                            continue;
                                                        }
                                                        refTable = MTable.Get(_ctx, findTable.GetAD_Table_ID());
                                                    }
                                                }
                                            }
                                            else
                                                refTable = m_MappedColsInOrder[cols].GetFKTable();


                                            //else
                                            //{


                                            if (refTable == null)
                                            {
                                                sbValue.Append("NULL").Append(", ");
                                                continue;
                                            }


                                            if (refTable.GetTableName().Equals("C_Location"))
                                            {
                                                //MLocation location = new MLocation(_ctx, 0, _trx.GetTrxName());
                                                //sbValue.Append(val).Append(", ");
                                                //continue;
                                            }

                                            if (m_finalMappedTables.Contains(refTable))
                                            {
                                                if (!IsTableMapped(m_WindowTables[tables]))
                                                {
                                                    int index = m_finalMappedTables.IndexOf(refTable);
                                                    sbValue.Append(m_ParentIDs[index][i]);
                                                }
                                                else
                                                {
                                                    StringBuilder colName = VLookUpFactory.GetLookup_DisplayColumn(Language.GetLanguage("en-US"), refTable.GetTableName());
                                                    //if (colName == null)
                                                    //colName = new StringBuilder("NAME");

                                                    //MColumn refIdentifier = refTable.GetIdentifierCol();
                                                    string dynSql = "SELECT " + refTable.GetTableName() + "_ID FROM " + refTable.GetTableName() + " WHERE UPPER(" + colName.ToString() + ") = UPPER('" + val + "')";
                                                    dynSql = GetOrgQuery(dynSql, refTable.GetTableName());
                                                    System.Data.IDataReader dr = DB.ExecuteReader(dynSql.ToString(), null, "ImportProcess");
                                                    int iis = 0;
                                                    string dynValue = "";
                                                    while (dr.Read())
                                                    {
                                                        iis++;
                                                        dynValue = dr[0].ToString();
                                                    }
                                                    dr.Close();
                                                    if (iis == 1)
                                                    {
                                                        sbValue.Append(dynValue);
                                                        val = dynValue;
                                                    }
                                                    else if (iis == 0)
                                                    {
                                                        sbValue.Append("NULL");
                                                    }
                                                    else
                                                    {

                                                        //m_multipleResults.Add(i);
                                                        anyError.Append("MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ")").Append(" IN ").Append(m_MappedColsInOrder[cols].GetFKTable().GetTableName()).Append(", ");

                                                        m_errorLog.Add(val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") IN " + m_MappedColsInOrder[cols].GetFKTable().GetTableName());
                                                        m_errorStatus.Add("MultipleRecordFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                                        m_errorInQuery.Add("MultipleRecordFoundFor: " + val);
                                                        sbValue.Append("NULL");
                                                    }
                                                }
                                            }
                                            else
                                            {

                                                StringBuilder colName = VLookUpFactory.GetLookup_DisplayColumn(Language.GetLanguage("en-US"), refTable.GetTableName());
                                                //string colName = m_identifierColumn[tables].GetColumnName();
                                                //if (colName == null)
                                                //    colName = new StringBuilder("NAME");
                                                //MColumn refIdentifier = refTable.GetIdentifierCol();
                                                string dynSql = "SELECT " + refTable.GetTableName() + "_ID FROM " + refTable.GetTableName() + " WHERE UPPER(" + colName.ToString() + ") = UPPER('" + val + "')";
                                                dynSql = GetOrgQuery(dynSql, refTable.GetTableName());

                                                System.Data.IDataReader dr = DB.ExecuteReader(dynSql.ToString(), null, "ImportProcess");
                                                int iis = 0;
                                                string dynValue = "";
                                                while (dr.Read())
                                                {
                                                    iis++;
                                                    dynValue = dr[0].ToString();
                                                }
                                                dr.Close();
                                                if (iis == 1)
                                                {
                                                    sbValue.Append(dynValue);
                                                    val = dynValue;
                                                }
                                                else if (iis == 0)
                                                {
                                                    //anyError.Append("CouldNotFindParentFor: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(",");
                                                    sbValue.Append("NULL");
                                                }
                                                else
                                                {
                                                    //m_duplicateKey.RemoveAt(m_duplicateKey.Count - 1);
                                                    //m_checkDuplicate.RemoveAt(m_checkDuplicate.Count - 1);
                                                    sbValue.Append("NULL");
                                                    //m_multipleResults.Add(i);

                                                    anyError.Append("MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ")").Append(" IN ").Append(m_MappedColsInOrder[cols].GetFKTable().GetTableName()).Append(", ");
                                                    m_errorLog.Add(val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") IN " + m_MappedColsInOrder[cols].GetFKTable().GetTableName());
                                                    m_errorStatus.Add("MultipleRecordFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                                    m_errorInQuery.Add("MultipleRecordFoundFor: " + val);
                                                    //sbValue.Append("NULL");
                                                    //m_insertErrors.Add("MultipleRecordsExistsAgainstOne");
                                                    //sbValue.Append("0");
                                                }
                                            }
                                            //}
                                        }
                                        catch
                                        {
                                        }

                                        //m_insertErrors.Add(GetMessage.GetMessageText("IDBlank", false));
                                        //return;
                                    }
                                    else if (m_MappedColsInOrder[cols].GetAD_Reference_ID() == FieldType.List)
                                    {
                                        int AD_Ref_ValueID = m_MappedColsInOrder[cols].GetAD_Reference_Value_ID();
                                        //MTable refTable = m_MappedColsInOrder[cols].GetFKTable();
                                        //string colName = VLookUpFactory.GetLookup_DisplayColumn(Language.GetLoginLanguage(), refTable.GetTableName()).ToString();
                                        string dynSql = "SELECT VALUE FROM AD_REF_LIST WHERE upper(NAME) = upper('" + val + "') AND AD_REFERENCE_ID = " + AD_Ref_ValueID;
                                        //dynSql += sWhereClause;
                                        System.Data.IDataReader dr = DB.ExecuteReader(dynSql.ToString(), null, "ImportProcess");
                                        int iis = 0;
                                        string dynValue = "";
                                        while (dr.Read())
                                        {
                                            iis++;
                                            dynValue = dr[0].ToString();
                                        }
                                        dr.Close();
                                        if (iis == 1)
                                        {
                                            sbValue.Append(GlobalVariable.TO_STRING(dynValue));
                                            val = dynValue;
                                        }
                                        else if (iis == 0)
                                        {
                                            //m_insertErrors.Add(GetMessage.GetMessageText("NoPrimaryKeyFound"));
                                            sbValue.Append("NULL");
                                        }
                                        else if (iis > 1)
                                        {
                                            //m_multipleResults.Add(i);
                                            anyError.Append("MutltipleListsFound: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(",");
                                            m_errorLog.Add(table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName());
                                            m_errorStatus.Add("MultipleListFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                            m_errorInQuery.Add("MultipleListFoundFor: " + val);
                                            sbValue.Append("NULL");
                                            //m_insertErrors.Add(GetMessage.GetMessageText("MultipleRecordsExistsAgainstOne"));
                                            //continue;
                                        }
                                    }
                                    else
                                    {
                                        sbValue.Append(GlobalVariable.TO_STRING(val));
                                    }

                                }
                                //****************************************Columns not mapped *******************************************
                                else          // Columns not mapped*************************************************************************
                                {
                                    String defaultValue = m_MappedColsInOrder[cols].GetDefaultValue();
                                    Object ob = DefaultValues[tables][m_MappedColsInOrder[cols].GetColumnName()];
                                    val = GlobalVariable.TO_STRING("");
                                    MTable refTable_1 = m_MappedColsInOrder[cols].GetFKTable();
                                    if (m_MappedColsInOrder[cols].GetColumnName().EndsWith("_ID") && !(m_MappedColsInOrder[cols].IsStandardColumn()) && !(m_MappedColsInOrder[cols].IsKey()))    //check if column is mandatory in database
                                    {
                                        if (m_MappedColsInOrder[cols].IsFK())
                                        {
                                            MTable fkTabel = m_MappedColsInOrder[cols].GetFKTable();
                                            if (m_finalMappedTables.Contains(fkTabel))
                                            {
                                                int index = m_finalMappedTables.IndexOf(fkTabel);
                                                if (index < m_finalMappedTables.IndexOf(m_WindowTables[tables]))
                                                    sbValue.Append(m_ParentIDs[index][i]);
                                            }
                                        }
                                    }



                                    if (m_MappedColsInOrder[cols].IsKey())  //if column is the primary key
                                    {
                                        val = key.ToString();
                                        sbValue.Append(key);
                                    }
                                    else if (m_MappedColsInOrder[cols].IsStringOrText())
                                    {
                                        if (ob == null)
                                        {
                                            if (IsDefaultValueSet(defaultValue))
                                            {
                                                val = GlobalVariable.TO_STRING(defaultValue);
                                                sbValue.Append(GlobalVariable.TO_STRING(defaultValue));
                                            }
                                            else if (m_MappedColsInOrder[cols].IsMandatoryUI())
                                            {
                                                anyError.Append("Mandatory: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(", ");
                                                m_errorLog.Add(table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName());
                                                m_errorStatus.Add("MandatoryField");
                                                m_errorInQuery.Add("Mapped Field has no value");

                                                val = GlobalVariable.TO_STRING("");
                                                sbValue.Append(GlobalVariable.TO_STRING(""));
                                            }
                                            else
                                            {
                                                val = GlobalVariable.TO_STRING("");
                                                sbValue.Append(GlobalVariable.TO_STRING(""));
                                            }
                                        }
                                        else
                                        {
                                            val = GlobalVariable.TO_STRING("");
                                            sbValue.Append(ob.ToString());
                                        }

                                    }
                                    else if (m_MappedColsInOrder[cols].IsYesNo())
                                    {
                                        if (ob == null)
                                        {
                                            if (m_MappedColsInOrder[cols].GetColumnName().Equals("IsActive", StringComparison.OrdinalIgnoreCase))
                                            {
                                                val = "Y";
                                                sbValue.Append(GlobalVariable.TO_STRING(val));
                                            }
                                            else
                                            {
                                                if (!string.IsNullOrEmpty(m_MappedColsInOrder[cols].GetSQLDefaultValue()))
                                                {
                                                    val = m_MappedColsInOrder[cols].GetSQLDefaultValue();
                                                    if (!val.StartsWith("'"))
                                                        sbValue.Append(GlobalVariable.TO_STRING(val));
                                                    else
                                                        sbValue.Append(m_MappedColsInOrder[cols].GetSQLDefaultValue());
                                                }
                                                else
                                                {
                                                    val = "N";
                                                    sbValue.Append(GlobalVariable.TO_STRING(val));
                                                }
                                            }
                                        }
                                        else
                                        {
                                            if (ob.Equals(false))
                                                val = "N";
                                            else
                                                val = "Y";

                                            sbValue.Append(GlobalVariable.TO_STRING(val));
                                        }
                                    }
                                    else if (m_MappedColsInOrder[cols].IsDate())
                                    {
                                        if (ob == null)
                                        {
                                            val = DateTime.Now.ToString("dd-MMM-yyyy");
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Today, true));
                                        }
                                        else
                                        {
                                            val = ob.ToString();
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Parse(ob.ToString()), true));
                                        }
                                    }
                                    else if (m_MappedColsInOrder[cols].IsDateTime())
                                    {
                                        if (ob == null)
                                        {
                                            val = DateTime.Now.ToString("dd-MMM-yyyy");
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Now, true));

                                        }
                                        else
                                        {
                                            val = ob.ToString();
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Parse(ob.ToString()), true));
                                        }
                                    }
                                    else if (m_MappedColsInOrder[cols].IsAD_Client_ID())
                                    {
                                        val = m_AD_Client_ID.ToString();
                                        sbValue.Append(m_AD_Client_ID);
                                    }
                                    else if (m_MappedColsInOrder[cols].IsAD_Org_ID())
                                    {
                                        val = m_AD_Org_ID.ToString();
                                        sbValue.Append(m_AD_Org_ID);
                                    }
                                    else if (FieldType.IsNumeric(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {
                                        if(ob == null)
                                        {
                                            if (IsDefaultValueSet(defaultValue))
                                            {
                                                val = defaultValue;
                                                sbValue.Append(defaultValue);
                                            }
                                            else
                                            {
                                                val = "0";
                                                sbValue.Append(0);
                                            }
                                        }
                                        else
                                        {
                                            val = ob.ToString();
                                            sbValue.Append(ob.ToString());
                                        }
                                    }
                                    else if (FieldType.IsID(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {
                                        if (refTable_1.GetTableName().Equals("C_Location"))
                                        {
                                            MLocation location = new MLocation(_ctx, 0, null);
                                            for (int v = 0; v < LocationMapping.m_LocationDBColumnName[tables].Count; v++)
                                            {
                                                String mappedColName = LocationMapping.m_LocationDBColumnName[tables][v];
                                                int excelColIndex = LocationMapping.m_LocationExcelIndex[tables][v];
                                                String mappedColValue = dt.Rows[i][excelColIndex].ToString();
                                                int mappedcolIndex = location.Get_ColumnIndex(mappedColName);


                                                MColumn[] columns = refTable_1.GetColumns(true);
                                                foreach (MColumn colum in columns)
                                                {
                                                    if (colum.GetName().Equals(mappedColName))
                                                    {
                                                        if ((colum.GetColumnName().Equals("C_City_ID", StringComparison.OrdinalIgnoreCase)) || (colum.GetColumnName().Equals("C_Country_ID", StringComparison.OrdinalIgnoreCase)))
                                                        {
                                                            String _sql = "Select " + colum.GetColumnName() + " From " + colum.GetFKTable().GetTableName() + " Where IsActive = 'Y' AND Upper(Name) = Upper('" + mappedColValue + "')";
                                                            Object o = DB.ExecuteScalar(_sql);
                                                            if (o != null)
                                                                mappedColValue = o.ToString();
                                                            else
                                                                mappedColValue = "";
                                                        }
                                                        location.Set_Value(colum.GetColumnName(), mappedColValue);
                                                        break;
                                                    }
                                                }

                                                
                                            }

                                            if (LocationMapping.m_LocationDBColumnName[tables].Count > 0)
                                            {
                                                bool b = location.Save();

                                                if (b)
                                                {
                                                    val = location.Get_ID().ToString();

                                                    //callback to the client
                                                    callback.QueryExecuted(new CallbackDetail()
                                                    {
                                                        Status = "Location Saved",
                                                        Query = "New Location saved with ID " + val,
                                                        Error = ""
                                                    });
                                                }
                                                else
                                                {
                                                    val = "NULL";
                                                }
                                            }
                                            else
                                            {
                                                val = "NULL";
                                            }

                                            sbValue.Append(val).Append(", ");
                                            continue;
                                        }
                                        else if (m_MappedColsInOrder[cols].IsCreatedBy() || m_MappedColsInOrder[cols].IsUpdatedBy())
                                        {
                                            if (ob == null)
                                            {
                                                sbValue.Append(_ctx.GetAD_User_ID());
                                                val = _ctx.GetAD_User_ID().ToString();
                                            }
                                            else
                                            {
                                                sbValue.Append(int.Parse(ob.ToString()));
                                                val = ob.ToString();
                                            }
                                        }
                                        else
                                        {
                                            MTable refTable = m_MappedColsInOrder[cols].GetFKTable();
                                            if (!m_finalMappedTables.Contains(refTable))
                                            {
                                                if (ob == null)
                                                {
                                                    if (IsDefaultValueSet(defaultValue) && !defaultValue.Equals("-1"))
                                                    {
                                                        val = defaultValue;
                                                        sbValue.Append(defaultValue);
                                                    }
                                                    else if (m_MappedColsInOrder[cols].IsMandatoryUI())
                                                    {
                                                        anyError.Append("Mandatory: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(", ");
                                                        m_errorLog.Add(table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName());
                                                        m_errorStatus.Add("MandatoryField");
                                                        m_errorInQuery.Add("Mapped Record has no value or Not Mapped");

                                                        val = "NULL";
                                                        sbValue.Append("NULL");
                                                    }
                                                    else
                                                    {
                                                        val = "NULL";
                                                        sbValue.Append("NULL");
                                                    }
                                                }
                                                else
                                                {
                                                    val = ob.ToString();
                                                    sbValue.Append(ob.ToString());

                                                }

                                            }
                                            else
                                            {
                                                if (sbValue[sbValue.Length - 1] == ',')
                                                {
                                                    if (ob == null)
                                                    {
                                                        val = "NULL";
                                                        sbValue.Append("NULL");
                                                    }
                                                    else
                                                    {
                                                        val = ob.ToString();
                                                        sbValue.Append(ob.ToString());
                                                    }

                                                }
                                            }
                                        }
                                        //m_insertErrors.Add(GetMessage.GetMessageText("IDBlank", false));
                                        //return;
                                    }
                                    else
                                    {
                                        if (IsDefaultValueSet(defaultValue))
                                        {
                                            val = GlobalVariable.TO_STRING(defaultValue);
                                            sbValue.Append(GlobalVariable.TO_STRING(defaultValue));

                                        }
                                        else
                                        {
                                            val = "NULL";
                                            sbValue.Append("NULL");
                                        }
                                    }
                                }
                            }
                            catch
                            {
                                sbValue.Append(GlobalVariable.TO_STRING(""));
                            }

                            row[cols] = val;
                            if (cols != m_MappedColsInOrder.Count - 1)
                                sbValue.Append(",");

                            //progValue
                            //if (cols * (100 / m_MappedColsInOrder.Count - 1) < 100)
                            //    bgWorker.ReportProgress((int)cols * (100 / m_MappedColsInOrder.Count - 1));
                            //else
                            //bgWorker.ReportProgress(progValue * (cols + 1));

                        }

                        //bgWorker.ReportProgress((i + 1) * 100);
                        sbValue.Append(")");
                        sbFinalQuery.Append(sql)
                            .Append(sbValue);

                        m_FinalData.Rows.Add(row);

                        if (isLoopBroken)
                        {
                            m_tempQueryHolder.Add(updateQuery);
                            isLoopBroken = false;
                        }
                        else
                        {
                            m_tempQueryHolder.Add(sbFinalQuery.ToString());
                        }
                        m_ParentKeys.Add(key);
                        if (anyError.Length > 0)
                        {
                            if (loopCount == 1)
                            {
                                m_insertErrors.Add(anyError.ToString());
                            }
                            else
                            {
                                if (m_insertErrors[i].Equals("NoError"))
                                    m_insertErrors[i] = anyError.ToString();
                                else
                                    m_insertErrors[i] += ", " + anyError.ToString();
                            }
                        }
                        else
                        {
                            if (loopCount == 1)
                            {
                                m_insertErrors.Add("NoError");
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        //success = false;
                        //m_insertErrors.Add(ex.Message);
                    }

                }
                List<string> temp_1 = m_tempQueryHolder.ToList();
                List<int> temp_2 = m_ParentKeys.ToList();

                m_ParentIDs.Add(temp_2);
                m_Queries.Add(temp_1);

                //if (!string.IsNullOrEmpty(anyError.ToString()))
                //    m_insertErrors.Add(anyError.ToString());
                //else
                //    m_insertErrors.Add("NoError");
            }


            #region condition
            for (int count = 0; count <= m_Queries.Count() - 1; count++)
            {
                if (m_Queries[0].Count != m_Queries[count].Count)
                {
                    //ShowMessage.Error("MappingNotCorrect", false);
                    m_QueiredTable.Clear();
                    m_ParentIDs.Clear();
                    m_Queries.Clear();
                    return;
                }
            }
            #endregion

            #region Test
            if (m_Queries.Count > 0)
            {
                int totalQueries = m_Queries[0].Count - 1;
                int totalTables = m_Queries.Count - 1;

                bool errorFound = false;
                
                for (int a = 0; a <= totalQueries; a++)
                {
                    if (!m_multipleResults.Contains(a))
                    {
                        for (int b = 0; b <= totalTables; b++)
                        {
                            CallbackDetail cb = new CallbackDetail();
                            string sql = m_Queries[b][a];
                            cb.Query = sql;
                            _trx = Trx.Get("ImportProcess", true);
                            //log.Info("Executing Import Query: " + sql);
                            int result = DB.ExecuteQuery(sql, null, "ImportProcess");
                            if (result == -1)
                            {
                                errorFound = true;
                                ValueNamePair error = VLogger.RetrieveError();
                                //log.Info(error.GetName());
                                m_errorLog.Add("ErrorOccured");
                                m_errorStatus.Add("ErrorExecutingQuery");
                                m_errorInQuery.Add(sql);
                                //m_errorLog.Add(sql);
                                m_insertErrors[a] = "ErrorOccured";
                                m_ErroredData.ImportRow(dt.Rows[a]);
                                cb.Error = "";
                                cb.Status = error.GetName();
                            }
                            else
                            {
                                //m_errorLog.Add(GetMessage.GetMessageText("SucessfulyExcecutedFollowingQuery"));
                                m_errorLog.Add("NoError");
                                m_errorStatus.Add("SucessfulyExcecutedQuery");
                                m_errorInQuery.Add(sql);

                                cb.Status = "NoError";
                                cb.Error = "";
                                

                                //log.Info("Import Query Excecuted");
                                if (b == totalTables)
                                    m_ImportedData.ImportRow(dt.Rows[a]);
                            }

                            callback.QueryExecuted(cb);
                        }
                    }
                    else
                    {
                        m_ErroredData.ImportRow(dt.Rows[a]);
                    }
                }

                CallbackDetail cb_done = new CallbackDetail();
                cb_done.Status = "Finished";
                cb_done.Query = "";

                if(errorFound)
                    cb_done.Error = "ImportDoneWithError";
                else
                    cb_done.Error = "ImportDoneWithoutError";

                callback.QueryExecuted(cb_done);
                if (_trx != null)
                {
                    if (LocationMapping.doCommit)
                    {
                        _trx.Commit();
                        _trx.Close();
                    }
                    else
                    {
                        _trx.Rollback();
                        _trx.Close();
                    }
                }

                //bgWorker.ReportProgress((dt.Rows.Count * 100) + 100);
            }
            else
                return;

            #endregion


            //_trx.Commit();
        }

        public void DoWork(ExcelMapping excelMapping, List<Dictionary<String, Object>> DefaultValues, OtherImportInfo other)
        {
            LocationMappingDetail LocationMapping = new LocationMappingDetail();
            ExcelReader reader = new ExcelReader(1, 1);
            System.Data.DataTable dt = reader.ExtractDataTable(FILE_PATH + "\\" + excelMapping.FileName, "Sheet1$");    //extract all the records form excel to DataTable

            if (dt.Rows.Count <= 0 || dt == null)
                return;

            //Remove Empty Rows From Datatable
            //for (int h = 0; h < dt.Rows.Count; h++)
            //{
            //    if (dt.Rows[h].IsNull(0) == true)
            //    {
            //      //  dt.Rows[h].Delete();
            //        return;
            //    }
            //}
            _ctx = new Ctx(excelMapping.CtxDic);

            m_ExcelColNames = excelMapping.ExcelCol;
            //m_WindowTables = 
            LocationMapping.doCommit = excelMapping.DoCommit;

            for (int a = 0; a < excelMapping.TabDetail.Count; a++)
            {
                m_identifierColumn.Add(null);

                //ExcelMappingDetail emd = other.AllTables[a].ColumnDetail.FirstOrDefault((ab) => ab.IsIdentifier == true);

                //if(emd != null)
                //{
                //    m_identifierColumn[a] = MColumn.Get(_ctx, emd.AD_Column_ID);
                //}
                
                LocationMapping.m_LocationDBColumnName = new List<List<string>>();
                LocationMapping.m_LocationExcelIndex = new List<List<int>>();

                m_WindowTables.Add(MTable.Get(_ctx, excelMapping.TabDetail[a].AD_Table_ID));
                if (excelMapping.TabDetail[a].ColumnDetail != null)
                {
                    for (int b = 0; b < excelMapping.TabDetail[a].ColumnDetail.Count; b++)
                    {
                        if (!excelMapping.TabDetail[a].ColumnDetail[b].ToDelete)
                        {
                            m_MappedTables.Add(excelMapping.TabDetail[a].AD_Table_ID);
                            m_MappedCols.Add(MColumn.Get(_ctx, excelMapping.TabDetail[a].ColumnDetail[b].AD_Column_ID));
                            m_MappedTabs.Add(MTable.Get(_ctx, excelMapping.TabDetail[a].AD_Table_ID));
                            m_MappedExcelCols.Add(dt.Columns.IndexOf(excelMapping.TabDetail[a].ColumnDetail[b].ExcelColName));
                            m_MappedColsName.Add(MColumn.Get(_ctx, excelMapping.TabDetail[a].ColumnDetail[b].AD_Column_ID).GetColumnName());
                            m_WindowTabs.Add(MTab.Get(_ctx, excelMapping.TabDetail[a].AD_Tab_ID));

                            if (excelMapping.TabDetail[a].ColumnDetail[b].IsIdentifier)
                            {
                                m_identifierColumn[a] = MColumn.Get(_ctx, excelMapping.TabDetail[a].ColumnDetail[b].AD_Column_ID);
                            }
                        }

                        m_MapIndex++;
                    }
                }


                //if (excelMapping.TabDetail[a].LocationMapping != null)
                //{
                //    for (int c = 0; c < excelMapping.TabDetail[a].LocationMapping.Count; c++)
                //    {
                        
                //        //LocationMapping.m_LocationExcelIndex.Add(excelMapping.TabDetail[a].LocationMapping[c].ExcelColIndex);
                //    }
                //}
            }


            if (callback == null)
                callback = OperationContext.Current.GetCallbackChannel<IImportCallback>();

            //callback to the client
            callback.QueryExecuted(new CallbackDetail()
            {
                Status = "Importing now..",
                Query = "",
                Error = ""
            });

            List<MTable> m_finalMappedTables = new List<MTable>();  //List of all the table Mapped in a window

            //following loop checks the table mapped and puts into m_finalMappedTables List.
            for (int tables = 0; tables <= m_WindowTables.Count - 1; tables++)
            {
                if (IsTableMapped(m_WindowTables[tables]))
                {
                    if (!m_finalMappedTables.Contains(m_WindowTables[tables]))
                        m_finalMappedTables.Add(m_WindowTables[tables]);
                }
            }
            int loopCount = 0;
            m_insertErrors.Clear(); //Clear all the previous errors from the List
            int m_AD_Client_ID = _ctx.GetAD_Client_ID();
            int m_AD_Org_ID = _ctx.GetAD_Org_ID();



            m_ImportedData = dt.Clone();
            m_ErroredData = dt.Clone();

            List<List<string>> m_ParentValue = new List<List<string>>();    //Window will have parent tab. so to keep parent tab ID's we'll use m_Parent Value List
            for (int tables = 0; tables <= m_WindowTables.Count - 1; tables++)
            {
                m_ParentValue.Add(new List<string>());
                string keyColumn = m_WindowTables[tables].GetTableName() + "_ID";
                string updateQuery = "UPDATE " + m_WindowTables[tables].GetTableName() + " SET " + keyColumn + " = 909 WHERE " + keyColumn + "= 909";
                if (!IsTableMapped(m_WindowTables[tables]) || m_QueiredTable.Contains(m_WindowTables[tables]))
                    continue;

                loopCount++;
                m_QueiredTable.Add(m_WindowTables[tables]);
                m_tempQueryHolder.Clear();
                m_ParentKeys.Clear();
                StringBuilder sql = new StringBuilder("");
                sql.Append(GetInitialInsertQuery(m_WindowTables[tables].Get_ID()));


                /////
                if (sql.ToString().EndsWith(")"))
                {
                    sql.Replace(")", "");
                }
                sql.Append(",Created,CreatedBy,Updated,UpdatedBy,AD_Client_ID,AD_Org_ID");
                sql.Append(")");
                /////

                int s_TotalRows = dt.Rows.Count;
                string val = "";
                MTable table = new MTable(_ctx, Convert.ToInt32(m_WindowTables[tables].GetAD_Table_ID()), null);
                //MColumn[] columns = table.GetColumns(true);

                m_FinalData = new System.Data.DataTable();

                //foreach (MColumn column in m_MappedColsInOrder)
                //{
                //    m_FinalData.Columns.Add(column.GetColumnName());
                //}

                List<string> m_checkDuplicate = new List<string>();
                List<int> m_duplicateKey = new List<int>();
                List<String> m_DuplicateParentValue = new List<String>();

                StringBuilder anyError = new StringBuilder("");
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    bool isLoopBroken = false;

                    anyError.Remove(0, anyError.Length);
                    mapCount = 0;
                    // System.Data.DataRow row = m_FinalData.NewRow();

                    try
                    {
                        //bgWorker.ReportProgress(0);

                        StringBuilder sbValue = new StringBuilder(" VALUES (");
                        StringBuilder sbFinalQuery = new StringBuilder();

                        int key = MSequence.GetNextID(m_AD_Client_ID, table.GetTableName(), "ImportProcess");
                        for (int cols = 0; cols <= m_MappedColsInOrder.Count - 1; cols++)
                        {
                            int progValue = 100 / (m_MappedColsInOrder.Count - 1);
                            try
                            {
                                //we will have two condition to check
                                //1. Columns which are mapped
                                //2. Columns which are not mapped
                                if (IsMapped(m_MappedColsInOrder[cols]))    //checks if column is mapped or not
                                {
                                    val = dt.Rows[i][m_CurrentExcelMapping[mapCount]].ToString();
                                    string identifierColumn = "";
                                    if (m_identifierColumn[tables] != null)
                                    {
                                        identifierColumn = m_identifierColumn[tables].GetColumnName();
                                    }

                                    if (m_MappedColsInOrder[cols].GetColumnName().Equals(identifierColumn, StringComparison.OrdinalIgnoreCase))
                                    {
                                        string checkValue = val;
                                        if (tables > 0)
                                        {
                                            int tableIndex = 0;
                                            for (int loop = 0; loop <= m_MappedColsInOrder.Count - 1; loop++)
                                            {
                                                if (m_MappedColsInOrder[loop].GetColumnName().EndsWith("_ID"))
                                                {
                                                    string colValue = m_MappedColsInOrder[loop].GetColumnName().Replace("_ID", "");
                                                    if (colValue.Equals(m_WindowTables[tables].GetTableName(), StringComparison.OrdinalIgnoreCase))
                                                        continue;

                                                    MTable foundTable = MTable.Get(_ctx, colValue);
                                                    if (m_finalMappedTables.Contains(foundTable))
                                                    {
                                                        tableIndex = m_finalMappedTables.IndexOf(foundTable);
                                                        break;
                                                    }
                                                }
                                            }
                                            checkValue = dt.Rows[i][MappedWith(m_identifierColumn[tableIndex])].ToString() + "|^|" + val;
                                        }

                                        if (m_checkDuplicate.Contains(checkValue))
                                        {
                                            key = m_duplicateKey[m_checkDuplicate.IndexOf(checkValue)];
                                            isLoopBroken = true;
                                            break;
                                        }



                                        //m_DuplicateTable.Add(m_WindowTables[tables]);
                                        String firstSql = "SELECT " + m_WindowTables[tables].GetTableName() + "_ID FROM " + m_WindowTables[tables].GetTableName();

                                        if (m_identifierColumn[tables] != null)
                                            firstSql += " WHERE " + m_identifierColumn[tables].GetColumnName() + "='" + val + "' ";


                                        if (tables > 0)
                                        {
                                            int tableIndex = 0;
                                            for (int loop = 0; loop <= m_MappedColsInOrder.Count - 1; loop++)
                                            {
                                                try
                                                {
                                                    if (m_MappedColsInOrder[loop].GetColumnName().EndsWith("_ID"))
                                                    {
                                                        string colValue = m_MappedColsInOrder[loop].GetColumnName().Replace("_ID", "");
                                                        if (colValue.Equals(m_WindowTables[tables].GetTableName(), StringComparison.OrdinalIgnoreCase))
                                                            continue;

                                                        MTable foundTable = MTable.Get(_ctx, colValue);

                                                        if (m_finalMappedTables.Contains(foundTable))
                                                        {
                                                            tableIndex = m_finalMappedTables.IndexOf(foundTable);
                                                            int id = m_ParentIDs[tableIndex][i];
                                                            firstSql = "SELECT " + m_WindowTables[tables].GetTableName() + "_ID FROM " + m_WindowTables[tables].GetTableName() + " WHERE " + m_finalMappedTables[tableIndex].GetTableName() + "_ID =" + id + " AND " + m_identifierColumn[tables].GetColumnName() + "='" + val + "' ";
                                                        }
                                                    }
                                                }
                                                catch(Exception ex)
                                                {
                                                    callback.QueryExecuted(new CallbackDetail()
                                                    {
                                                        Status = ex.Message,
                                                        Query = "",
                                                        Error = ""
                                                    });
                                                }
                                            }

                                        }
                                        firstSql = GetOrgQuery(firstSql, m_WindowTables[tables].GetTableName());
                                        System.Data.IDataReader dr = DB.ExecuteReader(firstSql);
                                        int val_id = 0;
                                        int j = 0;
                                        while (dr.Read())
                                        {
                                            val_id = int.Parse(dr[0].ToString());
                                            j++;

                                            if (j > 1)
                                            {
                                                //m_multipleResults.Add(i);
                                                string fkTable = m_WindowTables[tables].GetTableName();
                                                if (m_MappedColsInOrder[cols].GetFKTable() != null)
                                                    fkTable = m_MappedColsInOrder[cols].GetFKTable().GetTableName();
                                                //anyError.Append("MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ")").Append(" IN ").Append(fkTable).Append(", ");
                                                //callback to the client
                                                callback.QueryExecuted(new CallbackDetail()
                                                {
                                                    Status = "MutltipleRecordFoundFor" + val + "(" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") at " + "Row: " + i + 1 + " Col: " + cols + 1,
                                                    Query = "",
                                                    Error = ""
                                                });

                                                //m_errorLog.Add(val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") IN " + fkTable);
                                                //m_errorStatus.Add("MutltipleRecordFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                                //m_errorInQuery.Add("MultipleRecordFoundFor: " + val);
                                            }

                                        }
                                        dr.Close();

                                        //  Check if the record already exists in database    //
                                        string sqlCheck = "SELECT * FROM " + m_WindowTables[tables].GetTableName() + " WHERE " + m_MappedColsInOrder[cols].GetColumnName().ToUpper() + " = @val AND " + m_WindowTables[tables].GetTableName() + "_ID = " + val_id;
                                        sqlCheck = GetOrgQuery(sqlCheck, m_WindowTables[tables].GetTableName());
                                        System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[1];
                                        param[0] = new System.Data.SqlClient.SqlParameter("@val", val);
                                        dr = DB.ExecuteReader(sqlCheck, param);
                                        while (dr.Read())
                                        {
                                            key = int.Parse(dr[m_WindowTables[tables].GetTableName() + "_ID"].ToString());
                                            isLoopBroken = true;
                                            break;
                                        }
                                        dr.Close();

                                        m_duplicateKey.Add(key);
                                        m_checkDuplicate.Add(checkValue);

                                        if (isLoopBroken)
                                            break;
                                    }


                                    mapCount++;
                                    if (m_MappedColsInOrder[cols].IsStringOrText())
                                    {
                                        sbValue.Append(GlobalVariable.TO_STRING(val));
                                    }
                                    else if (m_MappedColsInOrder[cols].IsDateTime())
                                    {

                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Parse(val), m_MappedColsInOrder[cols].IsDate()));
                                            val = DateTime.Parse(val).ToString("dd-MMM-yyyy");
                                        }
                                        else
                                        {
                                            sbValue.Append("NULL");
                                            val = "NULL";

                                        }

                                    }
                                    else if (FieldType.IsNumeric(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {

                                        if (!string.IsNullOrEmpty(val))
                                            sbValue.Append(val);
                                    }
                                    else if (m_MappedColsInOrder[cols].IsYesNo())
                                    {
                                        if (!string.IsNullOrEmpty(val))
                                        {
                                            if (val.Equals("True", StringComparison.OrdinalIgnoreCase))
                                            {
                                                sbValue.Append(GlobalVariable.TO_STRING("Y"));
                                                //  row[cols] = "Y";
                                                val = "Y";
                                            }
                                            else if (val.Equals("Y", StringComparison.OrdinalIgnoreCase))
                                            {
                                                sbValue.Append(GlobalVariable.TO_STRING("Y"));
                                                //   row[cols] = "Y";
                                                val = "Y";
                                            }
                                            else
                                            {
                                                sbValue.Append(GlobalVariable.TO_STRING("N"));
                                                val = "N";
                                            }

                                        }
                                        else
                                        {
                                            sbValue.Append(m_MappedColsInOrder[cols].GetSQLDefaultValue());
                                            val = m_MappedColsInOrder[cols].GetSQLDefaultValue();
                                        }
                                    }
                                    else if (FieldType.IsID(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {
                                        MTable refTable = null;
                                        try
                                        {
                                            if (m_MappedColsInOrder[cols].GetAD_Reference_ID().Equals(30))
                                            {
                                                //If the column type is "SEARCH", we will try to find it through it ref value key.

                                                //Find the Reference Detail of a column
                                                MReference refOtherway = MReference.Get(_ctx, m_MappedColsInOrder[cols].GetAD_Reference_Value_ID());

                                                if (refOtherway.Get_ID().Equals(0)) //if null, just append null to the value query
                                                {
                                                    String FKTableName = m_MappedColsInOrder[cols].GetColumnName();
                                                    if (FKTableName.EndsWith("_ID"))
                                                        FKTableName = FKTableName.Substring(0, FKTableName.Length - 3);
                                                    refTable = MTable.Get(_ctx, FKTableName);
                                                    if (refTable == null)
                                                    {
                                                        sbValue.Append("NULL").Append(", ");
                                                        continue;
                                                    }
                                                }
                                                if (!refOtherway.Get_ID().Equals(0))
                                                {
                                                    if (refOtherway.GetValidationType().Equals("T"))    //run only in case of "T" Validation Type
                                                    {
                                                        //Find the Table refered in Validation 
                                                        MRefTable findTable = MRefTable.Get(_ctx, refOtherway.GetAD_Reference_ID());
                                                        if (findTable == null)
                                                        {
                                                            sbValue.Append("NULL").Append(", ");
                                                            continue;
                                                        }
                                                        refTable = MTable.Get(_ctx, findTable.GetAD_Table_ID());
                                                    }
                                                }
                                            }
                                            else
                                                refTable = m_MappedColsInOrder[cols].GetFKTable();


                                            //else
                                            //{


                                            if (refTable == null)
                                            {
                                                sbValue.Append("NULL").Append(", ");
                                                continue;
                                            }


                                            if (refTable.GetTableName().Equals("C_Location"))
                                            {
                                                //MLocation location = new MLocation(_ctx, 0, _trx.GetTrxName());
                                                //sbValue.Append(val).Append(", ");
                                                //continue;
                                            }

                                            if (m_finalMappedTables.Contains(refTable))
                                            {
                                                if (!IsTableMapped(m_WindowTables[tables]))
                                                {
                                                    int index = m_finalMappedTables.IndexOf(refTable);
                                                    sbValue.Append(m_ParentIDs[index][i]);
                                                }
                                                else
                                                {
                                                    StringBuilder colName = VLookUpFactory.GetLookup_DisplayColumn(Language.GetLanguage("en-US"), refTable.GetTableName());
                                                    //if (colName == null)
                                                    //colName = new StringBuilder("NAME");

                                                    //MColumn refIdentifier = refTable.GetIdentifierCol();
                                                    string dynSql = "SELECT " + refTable.GetTableName() + "_ID FROM " + refTable.GetTableName() + " WHERE UPPER(" + colName.ToString() + ") = UPPER(" + DB.TO_STRING(val) + ")";
                                                    dynSql = GetOrgQuery(dynSql, refTable.GetTableName());
                                                    System.Data.IDataReader dr = DB.ExecuteReader(dynSql.ToString(), null, "ImportProcess");
                                                    int iis = 0;
                                                    string dynValue = "";
                                                    while (dr.Read())
                                                    {
                                                        iis++;
                                                        dynValue = dr[0].ToString();
                                                    }
                                                    dr.Close();
                                                    if (iis == 1)
                                                    {
                                                        sbValue.Append(dynValue);
                                                        val = dynValue;
                                                    }
                                                    else if (iis == 0)
                                                    {
                                                        sbValue.Append("NULL");
                                                    }
                                                    else
                                                    {

                                                        //m_multipleResults.Add(i);
                                                        //anyError.Append("MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ")").Append(" IN ").Append(m_MappedColsInOrder[cols].GetFKTable().GetTableName()).Append(", ");

                                                        //m_errorLog.Add(val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") IN " + m_MappedColsInOrder[cols].GetFKTable().GetTableName());
                                                        //m_errorStatus.Add("MultipleRecordFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                                        //m_errorInQuery.Add("MultipleRecordFoundFor: " + val);
                                                        sbValue.Append("NULL");

                                                        callback.QueryExecuted(new CallbackDetail()
                                                        {
                                                            Status = "MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") at Row: " + i + 1 + " Col: " + cols + 1,
                                                            Query = "",
                                                            Error = ""
                                                        });
                                                    }
                                                }
                                            }
                                            else
                                            {

                                                StringBuilder colName = VLookUpFactory.GetLookup_DisplayColumn(Language.GetLanguage("en-US"), refTable.GetTableName());
                                                //string colName = m_identifierColumn[tables].GetColumnName();
                                                //if (colName == null)
                                                //    colName = new StringBuilder("NAME");
                                                //MColumn refIdentifier = refTable.GetIdentifierCol();

                                                string dynSql = "";

                                                //////////////
                                                ///////////// chnage for AD_language_ID
                                                if (m_MappedColsInOrder[cols].GetColumnName().Equals("AD_Language"))
                                                {
                                                    dynSql = "SELECT AD_LANGUAGE FROM AD_language WHERE UPPER(" + colName.ToString() + ") = UPPER(" + DB.TO_STRING(val) + ")";
                                                }
                                                //////////////
                                                //////////////
                                                else
                                                {
                                                    dynSql = "SELECT " + refTable.GetTableName() + "_ID FROM " + refTable.GetTableName() + " WHERE UPPER(" + colName.ToString() + ") = UPPER(" + DB.TO_STRING(val) + ")";
                                                }
                                                dynSql = GetOrgQuery(dynSql, refTable.GetTableName());

                                                System.Data.IDataReader dr = DB.ExecuteReader(dynSql.ToString(), null, "ImportProcess");
                                                int iis = 0;
                                                string dynValue = "";
                                                while (dr.Read())
                                                {
                                                    iis++;
                                                    dynValue = dr[0].ToString();
                                                }
                                                dr.Close();
                                                if (iis == 1)
                                                {
                                                    if (m_MappedColsInOrder[cols].GetColumnName().Equals("AD_Language"))
                                                    {
                                                        sbValue.Append("'" + dynValue + "'");
                                                    }
                                                    else
                                                    {
                                                        sbValue.Append(dynValue);
                                                    }
                                                    val = dynValue;
                                                }
                                                else if (iis == 0)
                                                {
                                                    //anyError.Append("CouldNotFindParentFor: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(",");
                                                    sbValue.Append("NULL");
                                                }
                                                else
                                                {
                                                    //m_duplicateKey.RemoveAt(m_duplicateKey.Count - 1);
                                                    //m_checkDuplicate.RemoveAt(m_checkDuplicate.Count - 1);
                                                    sbValue.Append("NULL");

                                                    callback.QueryExecuted(new CallbackDetail()
                                                    {
                                                        Status = "MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") at Row: " + i + 1 + " Col: " + cols + 1,
                                                        Query = "",
                                                        Error = ""
                                                    });

                                                    //m_multipleResults.Add(i);

                                                    //anyError.Append("MutltipleRecordFoundFor " + val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ")").Append(" IN ").Append(m_MappedColsInOrder[cols].GetFKTable().GetTableName()).Append(", ");
                                                    //m_errorLog.Add(val + " (" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") IN " + m_MappedColsInOrder[cols].GetFKTable().GetTableName());
                                                    //m_errorStatus.Add("MultipleRecordFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                                    //m_errorInQuery.Add("MultipleRecordFoundFor: " + val);
                                                    //sbValue.Append("NULL");
                                                    //m_insertErrors.Add("MultipleRecordsExistsAgainstOne");
                                                    //sbValue.Append("0");
                                                }
                                            }
                                            //}
                                        }
                                        catch(Exception ex)
                                        {
                                            callback.QueryExecuted(new CallbackDetail()
                                            {
                                                Status = ex.Message,
                                                Query = "",
                                                Error = ""
                                            });
                                        }

                                        //m_insertErrors.Add(GetMessage.GetMessageText("IDBlank", false));
                                        //return;
                                    }
                                    else if (m_MappedColsInOrder[cols].GetAD_Reference_ID() == FieldType.List)
                                    {
                                        int AD_Ref_ValueID = m_MappedColsInOrder[cols].GetAD_Reference_Value_ID();
                                        //MTable refTable = m_MappedColsInOrder[cols].GetFKTable();
                                        //string colName = VLookUpFactory.GetLookup_DisplayColumn(Language.GetLoginLanguage(), refTable.GetTableName()).ToString();
                                        string dynSql = "SELECT VALUE FROM AD_REF_LIST WHERE upper(NAME) = upper('" + val + "') AND AD_REFERENCE_ID = " + AD_Ref_ValueID;
                                        //dynSql += sWhereClause;
                                        System.Data.IDataReader dr = DB.ExecuteReader(dynSql.ToString(), null, "ImportProcess");
                                        int iis = 0;
                                        string dynValue = "";
                                        while (dr.Read())
                                        {
                                            iis++;
                                            dynValue = dr[0].ToString();
                                        }
                                        dr.Close();
                                        if (iis == 1)
                                        {
                                            sbValue.Append(GlobalVariable.TO_STRING(dynValue));
                                            val = dynValue;
                                        }
                                        else if (iis == 0)
                                        {
                                            //m_insertErrors.Add(GetMessage.GetMessageText("NoPrimaryKeyFound"));
                                            sbValue.Append("NULL");
                                        }
                                        else if (iis > 1)
                                        {
                                            //m_multipleResults.Add(i);

                                            //anyError.Append("MutltipleListsFound: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(",");
                                            //m_errorLog.Add(table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName());
                                            //m_errorStatus.Add("MultipleListFound" + " " + "Row: " + i + 1 + " Col: " + cols + 1);
                                            //m_errorInQuery.Add("MultipleListFoundFor: " + val);
                                            sbValue.Append("NULL");

                                            callback.QueryExecuted(new CallbackDetail()
                                            {
                                                Status = "MutltipleRecordFoundFor " + val + "(" + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName() + ") at Row: " + i + 1 + " Col: " + cols + 1,
                                                Query = "",
                                                Error = ""
                                            });

                                            //m_insertErrors.Add(GetMessage.GetMessageText("MultipleRecordsExistsAgainstOne"));
                                            //continue;
                                        }
                                    }
                                    else
                                    {
                                        sbValue.Append(GlobalVariable.TO_STRING(val));
                                    }

                                }
                                //****************************************Columns not mapped *******************************************
                                else          // Columns not mapped*************************************************************************
                                {
                                    String defaultValue = m_MappedColsInOrder[cols].GetDefaultValue();
                                   
                                    if (DefaultValues[tables].Count <= 0)
                                    {
                                        Dictionary<String, Object> setdef = new Dictionary<string, object>();
                                        for (int s = 0; s < other.AllTables[tables].ColumnDetail.Count(); s++)
                                        {
                                            setdef.Add(other.AllTables[tables].ColumnDetail[s].AD_Column_Name, null);
                                        }

                                        DefaultValues[tables] = setdef;
                                    }

                                    if (DefaultValues[tables].Count != other.AllTables[tables].ColumnDetail.Count())
                                    {
                                        Dictionary<String, Object> setdef = new Dictionary<string, object>();
                                        for (int s = 0; s < other.AllTables[tables].ColumnDetail.Count(); s++)
                                        {
                                            if(DefaultValues[tables].ContainsKey(other.AllTables[tables].ColumnDetail[s].AD_Column_Name))
                                            {
                                                setdef.Add(other.AllTables[tables].ColumnDetail[s].AD_Column_Name, DefaultValues[tables][other.AllTables[tables].ColumnDetail[s].AD_Column_Name]);
                                            }
                                            else
                                            {
                                                 setdef.Add(other.AllTables[tables].ColumnDetail[s].AD_Column_Name, null);
                                            }
                                        }

                                        DefaultValues[tables] = setdef;
                                    }

                                    Object ob = DefaultValues[tables][m_MappedColsInOrder[cols].GetColumnName()];
                                    val = GlobalVariable.TO_STRING("");
                                    MTable refTable_1 = m_MappedColsInOrder[cols].GetFKTable();
                                    if (m_MappedColsInOrder[cols].GetColumnName().EndsWith("_ID") && !(m_MappedColsInOrder[cols].IsStandardColumn()) && !(m_MappedColsInOrder[cols].IsKey()))    //check if column is mandatory in database
                                    {
                                        if (m_MappedColsInOrder[cols].IsFK())
                                        {
                                            MTable fkTabel = m_MappedColsInOrder[cols].GetFKTable();
                                            if (m_finalMappedTables.Contains(fkTabel))
                                            {
                                                int index = m_finalMappedTables.IndexOf(fkTabel);
                                                if (index < m_finalMappedTables.IndexOf(m_WindowTables[tables]))
                                                    sbValue.Append(m_ParentIDs[index][i]);
                                            }
                                        }
                                    }



                                    if (m_MappedColsInOrder[cols].IsKey())  //if column is the primary key
                                    {
                                        val = key.ToString();
                                        sbValue.Append(key);                                     
                                      
                                    }
                                    else if (m_MappedColsInOrder[cols].IsStringOrText())
                                    {
                                        if (ob == null)
                                        {
                                            if (IsDefaultValueSet(defaultValue))
                                            {
                                                val = GlobalVariable.TO_STRING(defaultValue);
                                                sbValue.Append(GlobalVariable.TO_STRING(defaultValue));
                                            }
                                            else if (m_MappedColsInOrder[cols].IsMandatoryUI())
                                            {
                                                //anyError.Append("Mandatory: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(", ");
                                                //m_errorLog.Add(table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName());
                                                //m_errorStatus.Add("MandatoryField");
                                                //m_errorInQuery.Add("Mapped Field has no value");

                                                val = GlobalVariable.TO_STRING("");
                                                sbValue.Append(GlobalVariable.TO_STRING(""));
                                            }
                                            else
                                            {
                                                val = GlobalVariable.TO_STRING("");
                                                sbValue.Append(GlobalVariable.TO_STRING(""));
                                            }
                                        }
                                        else
                                        {
                                            val = GlobalVariable.TO_STRING("");
                                            sbValue.Append(GlobalVariable.TO_STRING(ob.ToString()));
                                        }

                                    }
                                    else if (m_MappedColsInOrder[cols].IsYesNo())
                                    {
                                        if (ob == null)
                                        {
                                            if (m_MappedColsInOrder[cols].GetColumnName().Equals("IsActive", StringComparison.OrdinalIgnoreCase))
                                            {
                                                val = "Y";
                                                sbValue.Append(GlobalVariable.TO_STRING(val));
                                            }
                                            else
                                            {
                                                if (!string.IsNullOrEmpty(m_MappedColsInOrder[cols].GetSQLDefaultValue()))
                                                {
                                                    val = m_MappedColsInOrder[cols].GetSQLDefaultValue();
                                                    if (!val.StartsWith("'"))
                                                        sbValue.Append(GlobalVariable.TO_STRING(val));
                                                    else
                                                        sbValue.Append(m_MappedColsInOrder[cols].GetSQLDefaultValue());
                                                }
                                                else
                                                {
                                                    val = "N";
                                                    sbValue.Append(GlobalVariable.TO_STRING(val));
                                                }
                                            }
                                        }
                                        else
                                        {
                                            if (ob.Equals(false) || ob.Equals("False"))
                                                val = "N";
                                            else
                                                val = "Y";

                                            sbValue.Append(GlobalVariable.TO_STRING(val));
                                        }
                                    }
                                    else if (m_MappedColsInOrder[cols].IsDate())
                                    {
                                        if (ob == null)
                                        {
                                            val = DateTime.Now.ToString("dd-MMM-yyyy");
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Today, true));
                                        }
                                        else
                                        {
                                            val = ob.ToString();
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Parse(ob.ToString()), true));
                                        }
                                    }
                                    else if (m_MappedColsInOrder[cols].IsDateTime())
                                    {
                                        if (ob == null)
                                        {
                                            val = DateTime.Now.ToString("dd-MMM-yyyy");
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Now, true));

                                        }
                                        else
                                        {
                                            val = ob.ToString();
                                            sbValue.Append(GlobalVariable.TO_DATE(DateTime.Parse(ob.ToString()), true));
                                        }
                                    }
                                    else if (m_MappedColsInOrder[cols].IsAD_Client_ID())
                                    {
                                        val = m_AD_Client_ID.ToString();
                                        sbValue.Append(m_AD_Client_ID);
                                    }
                                    else if (m_MappedColsInOrder[cols].IsAD_Org_ID())
                                    {
                                        val = m_AD_Org_ID.ToString();
                                        sbValue.Append(m_AD_Org_ID);
                                    }
                                    else if (FieldType.IsNumeric(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {
                                        if (ob == null)
                                        {
                                            if (IsDefaultValueSet(defaultValue))
                                            {
                                                val = defaultValue;
                                                sbValue.Append(defaultValue);
                                            }
                                            else
                                            {
                                                if (m_MappedColsInOrder[cols].GetColumnName().Equals("Enroll_ID", StringComparison.OrdinalIgnoreCase))
                                                {
                                                    val = "NULL";
                                                }
                                                else
                                                {
                                                    val = "0";
                                                }
                                                                                                
                                                sbValue.Append(val.ToString());
                                            }
                                        }
                                        else
                                        {
                                            val = ob.ToString();
                                            sbValue.Append(ob.ToString());
                                        }
                                    }
                                    else if (FieldType.IsID(m_MappedColsInOrder[cols].GetAD_Reference_ID()))
                                    {
                                        if (refTable_1 == null)
                                        {
                                            val = "NULL";
                                            sbValue.Append(val.ToString())
                                                .Append(",");

                                            continue;
                                        }

                                        if (refTable_1.GetTableName().Equals("C_Location"))
                                        {
                                            //MLocation location = new MLocation(_ctx, 0, null);
                                            MLocation location = new MLocation(_ctx, 0, "ImportProcess");
                                            if (excelMapping.TabDetail[tables].LocationMapping != null)
                                            {

                                                for (int v = 0; v < excelMapping.TabDetail[tables].LocationMapping.Count; v++)
                                                {
                                                    String mappedColName = excelMapping.TabDetail[tables].LocationMapping[v].AD_Column_Name;
                                                    int excelColIndex = dt.Columns.IndexOf(excelMapping.TabDetail[tables].LocationMapping[v].ExcelColName);
                                                    String mappedColValue = dt.Rows[i][excelColIndex].ToString();
                                                    int mappedcolIndex = location.Get_ColumnIndex(mappedColName);


                                                    MColumn[] columns = refTable_1.GetColumns(true);
                                                    foreach (MColumn colum in columns)
                                                    {
                                                        if (colum.GetColumnName().Equals(mappedColName))
                                                        {
                                                            if ((colum.GetColumnName().Equals("C_City_ID", StringComparison.OrdinalIgnoreCase)) || (colum.GetColumnName().Equals("C_Country_ID", StringComparison.OrdinalIgnoreCase)))
                                                            {
                                                                String _sql = "Select " + colum.GetColumnName() + " From " + colum.GetFKTable().GetTableName() + " Where IsActive = 'Y' AND Upper(Name) = Upper('" + mappedColValue + "')";
                                                                Object o = DB.ExecuteScalar(_sql);
                                                                if (o != null)
                                                                    mappedColValue = o.ToString();
                                                                else
                                                                    mappedColValue = "";

                                                                if (colum.GetColumnName().Equals("C_City_ID", StringComparison.OrdinalIgnoreCase))
                                                                {
                                                                    location.SetC_City_ID(Convert.ToInt32(mappedColValue));
                                                                }
                                                                else
                                                                {
                                                                    location.SetC_Country_ID(Convert.ToInt32(mappedColValue));
                                                                }


                                                                break;

                                                            }
                                                            else
                                                            {
                                                                location.Set_Value(colum.GetColumnName(), mappedColValue);
                                                                break;
                                                            }
                                                        }
                                                    }


                                                }
                                            }

                                            if (excelMapping.TabDetail[tables].LocationMapping != null)
                                            {

                                                if (excelMapping.TabDetail[tables].LocationMapping.Count > 0)
                                                {
                                                    bool b = location.Save();

                                                    if (b)
                                                    {
                                                        val = location.Get_ID().ToString();

                                                        //callback to the client
                                                        callback.QueryExecuted(new CallbackDetail()
                                                        {
                                                            Status = "Location Saved",
                                                            Query = "New Location saved with ID " + val,
                                                            Error = ""
                                                        });
                                                    }
                                                    else
                                                    {
                                                        val = "NULL";
                                                    }
                                                }
                                                else
                                                {
                                                    val = "NULL";
                                                }
                                            }
                                            else
                                            {
                                                val = "NULL";
                                            }

                                            sbValue.Append(val).Append(", ");
                                            continue;
                                        }
                                        else if (m_MappedColsInOrder[cols].IsCreatedBy() || m_MappedColsInOrder[cols].IsUpdatedBy())
                                        {
                                            if (ob == null)
                                            {
                                                sbValue.Append(_ctx.GetAD_User_ID());
                                                val = _ctx.GetAD_User_ID().ToString();
                                            }
                                            else
                                            {
                                                sbValue.Append(int.Parse(ob.ToString()));
                                                val = ob.ToString();
                                            }
                                        }
                                        else
                                        {
                                            MTable refTable = m_MappedColsInOrder[cols].GetFKTable();
                                            if (!m_finalMappedTables.Contains(refTable))
                                            {
                                                if (ob == null)
                                                {
                                                    if (IsDefaultValueSet(defaultValue) && !defaultValue.Equals("-1"))
                                                    {
                                                        val = defaultValue;
                                                        sbValue.Append(defaultValue);
                                                    }
                                                    else if (m_MappedColsInOrder[cols].IsMandatoryUI())
                                                    {
                                                        //anyError.Append("Mandatory: " + table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName()).Append(", ");
                                                        //m_errorLog.Add(table.GetTableName() + "." + m_MappedColsInOrder[cols].GetColumnName());
                                                        //m_errorStatus.Add("MandatoryField");
                                                        //m_errorInQuery.Add("Mapped Record has no value or Not Mapped");

                                                        val = "NULL";
                                                        sbValue.Append("NULL");
                                                    }
                                                    else
                                                    {
                                                        val = "NULL";
                                                        sbValue.Append("NULL");
                                                    }
                                                }
                                                else
                                                {
                                                    if (m_MappedColsInOrder[cols].GetColumnName().Equals("AD_Language"))
                                                    {
                                                        val = "'" + ob.ToString() + "'";
                                                    }
                                                    else
                                                    {
                                                        val = ob.ToString();
                                                    }
                                                    sbValue.Append(val);

                                                }

                                            }
                                            else
                                            {
                                                if (sbValue[sbValue.Length - 1] == ',')
                                                {
                                                    if (ob == null)
                                                    {
                                                        val = "NULL";
                                                        sbValue.Append("NULL");
                                                    }
                                                    else
                                                    {
                                                        val = ob.ToString();
                                                        sbValue.Append(ob.ToString());
                                                    }

                                                }
                                            }
                                        }
                                        //m_insertErrors.Add(GetMessage.GetMessageText("IDBlank", false));
                                        //return;
                                    }
                                    else
                                    {
                                        if (IsDefaultValueSet(defaultValue))
                                        {
                                            val = GlobalVariable.TO_STRING(defaultValue);
                                            sbValue.Append(GlobalVariable.TO_STRING(defaultValue));

                                        }
                                        else
                                        {
                                            val = "NULL";
                                            sbValue.Append("NULL");
                                        }
                                    }
                                }
                            }
                            catch
                            {
                                sbValue.Append(GlobalVariable.TO_STRING(""));
                            }

                           // row[cols] = val;
                            if (cols != m_MappedColsInOrder.Count - 1)
                                sbValue.Append(",");

                            //progValue
                            //if (cols * (100 / m_MappedColsInOrder.Count - 1) < 100)
                            //    bgWorker.ReportProgress((int)cols * (100 / m_MappedColsInOrder.Count - 1));
                            //else
                            //bgWorker.ReportProgress(progValue * (cols + 1));

                        }


                        //set Default Columns such as created,createdBy
                        if (!sbValue.ToString().EndsWith(","))
                        {
                            sbValue.Append(",");
                        }
                        sbValue.Append(GlobalVariable.TO_DATE(DateTime.Today, true));
                        sbValue.Append(",");
                        sbValue.Append(_ctx.GetAD_User_ID());
                        sbValue.Append(",");
                        sbValue.Append(GlobalVariable.TO_DATE(DateTime.Today, true));
                        sbValue.Append(",");
                        sbValue.Append(_ctx.GetAD_User_ID());
                        sbValue.Append(",");
                        sbValue.Append(m_AD_Client_ID);
                        sbValue.Append(",");         
                        sbValue.Append(m_AD_Org_ID);
                        //bgWorker.ReportProgress((i + 1) * 100);
                        sbValue.Append(")");
                        sbFinalQuery.Append(sql)
                            .Append(sbValue);

                       // m_FinalData.Rows.Add(row);

                        if (isLoopBroken)
                        {
                            m_tempQueryHolder.Add(updateQuery);
                            isLoopBroken = false;
                        }
                        else
                        {
                            m_tempQueryHolder.Add(sbFinalQuery.ToString());
                        }
                        m_ParentKeys.Add(key);
                        if (anyError.Length > 0)
                        {
                            if (loopCount == 1)
                            {
                                m_insertErrors.Add(anyError.ToString());
                            }
                            else
                            {
                                if (m_insertErrors[i].Equals("NoError"))
                                    m_insertErrors[i] = anyError.ToString();
                                else
                                    m_insertErrors[i] += ", " + anyError.ToString();
                            }
                        }
                        else
                        {
                            if (loopCount == 1)
                            {
                                m_insertErrors.Add("NoError");
                            }
                        }

                    }
                    catch (Exception ex)
                    {
                        callback.QueryExecuted(new CallbackDetail()
                        {
                            Status = ex.Message,
                            Query = "",
                            Error = ""
                        });
                    }

                }
                List<string> temp_1 = m_tempQueryHolder.ToList();
                List<int> temp_2 = m_ParentKeys.ToList();

                m_ParentIDs.Add(temp_2);
                m_Queries.Add(temp_1);

                //if (!string.IsNullOrEmpty(anyError.ToString()))
                //    m_insertErrors.Add(anyError.ToString());
                //else
                //    m_insertErrors.Add("NoError");
            }


            #region condition
            for (int count = 0; count <= m_Queries.Count() - 1; count++)
            {
                if (m_Queries[0].Count != m_Queries[count].Count)
                {
                    //ShowMessage.Error("MappingNotCorrect", false);
                    m_QueiredTable.Clear();
                    m_ParentIDs.Clear();
                    m_Queries.Clear();
                    return;
                }
            }
            #endregion

            #region Test
            if (m_Queries.Count > 0)
            {
                int totalQueries = m_Queries[0].Count - 1;
                int totalTables = m_Queries.Count - 1;

                bool errorFound = false;

                for (int a = 0; a <= totalQueries; a++)
                {
                    if (!m_multipleResults.Contains(a))
                    {
                        for (int b = 0; b <= totalTables; b++)
                        {
                            CallbackDetail cb = new CallbackDetail();
                            string sql = m_Queries[b][a];
                            if (sql.StartsWith("update", StringComparison.OrdinalIgnoreCase))
                            {
                                // cb.Query = sql;
                                cb.Query = "Record Already Exists. Nothing Imported";
                            }
                            else
                            {
                                cb.Query = sql;
                            }
                            _trx = Trx.Get("ImportProcess", true);
                            //log.Info("Executing Import Query: " + sql);
                            int result = DB.ExecuteQuery(sql, null, "ImportProcess");
                            if (result == -1)
                            {
                                errorFound = true;
                                ValueNamePair error = VLogger.RetrieveError();
                                //log.Info(error.GetName());
                                //m_errorLog.Add("ErrorOccured");
                                //m_errorStatus.Add("ErrorExecutingQuery");
                                //m_errorInQuery.Add(sql);
                                //m_errorLog.Add(sql);
                                //m_insertErrors[a] = "ErrorOccured";
                                m_ErroredData.ImportRow(dt.Rows[a]);
                                cb.Error = "";
                                cb.Status = error.GetName();
                            }
                            else
                            {
                                //m_errorLog.Add(GetMessage.GetMessageText("SucessfulyExcecutedFollowingQuery"));
                                //m_errorLog.Add("NoError");
                                //m_errorStatus.Add("SucessfulyExcecutedQuery");
                                //m_errorInQuery.Add(sql);

                                cb.Status = "NoError";
                                cb.Error = "";


                                //log.Info("Import Query Excecuted");
                                //if (b == totalTables)
                                //    m_ImportedData.ImportRow(dt.Rows[a]);
                            }

                            callback.QueryExecuted(cb);
                        }
                    }
                    else
                    {
                        //m_ErroredData.ImportRow(dt.Rows[a]);
                    }
                }

                CallbackDetail cb_done = new CallbackDetail();
                cb_done.Status = "Finished";
                cb_done.Query = "";

                if (errorFound)
                    cb_done.Error = "ImportDoneWithError";
                else
                    cb_done.Error = "ImportDoneSucessfully";

                callback.QueryExecuted(cb_done);
                if (_trx != null)
                {
                    if (excelMapping.DoCommit)
                    {
                        _trx.Commit();
                        _trx.Close();
                    }
                    else
                    {
                        _trx.Rollback();
                        _trx.Close();
                    }
                }

                //bgWorker.ReportProgress((dt.Rows.Count * 100) + 100);
            }
            else
                return;

            #endregion


            //_trx.Commit();
        }

        //Trx _trx = null;

        public List<int> m_MappedTables = new List<int>();
        /// <summary>
        /// Check if any column of table is mapped
        /// </summary>
        /// <param name="table">Table Name</param>
        /// <returns>True or False</returns>
        private bool IsTableMapped(MTable table)
        {
            if (m_MappedTables.Contains(table.Get_ID()))
                return true;

            return false;
        }
        #endregion
    }
}
